<?php
class ModelRestaurantRestaurant extends Model {
	public function updateViewed($restaurant_id) {
		$this->db->query("UPDATE " . DB_PREFIX . "restaurant SET viewed = (viewed + 1) WHERE restaurant_id = '" . (int)$restaurant_id . "'");
	}
	
	public function getRestaurant($restaurant_id) {
		$sql = "SELECT *, " .
			   "	(SELECT AVG(rating) AS total FROM " . DB_PREFIX . "restaurant_review r1 WHERE r1.restaurant_id = r.restaurant_id AND r1.status = '1' GROUP BY r1.restaurant_id) AS rating," .
			   " 	(SELECT COUNT(*) AS total FROM " . DB_PREFIX . "restaurant_review r2 WHERE r2.restaurant_id = r.restaurant_id AND r2.status = '1' GROUP BY r2.restaurant_id) AS reviews" .
			   " FROM " . DB_PREFIX . "restaurant r LEFT JOIN " . DB_PREFIX . "restaurant_description rd ON r.restaurant_id = rd.restaurant_id" .
			   " WHERE r.restaurant_id = '" . (int)$restaurant_id . "'" .
			   " AND rd.language_id = '" . (int)$this->config->get('config_language_id') . "'";
		$query = $this->db->query($sql);
		
		if ($query->num_rows) {
			$query->row['rating'] = (int)$query->row['rating'];
			
			// Settings
			$setting_query = $this->db->query("SELECT * FROM " . DB_PREFIX . "setting WHERE restaurant_id = '" . (int)$restaurant_id . "'");
			
			// Config
			$config = new Config();
			foreach ($setting_query->rows as $setting) {
				if (!$setting['serialized']) {
					$config->set($setting['key'], $setting['value']);
				} else {
					$config->set($setting['key'], unserialize($setting['value']));
				}
			}
			
			$query->row['config'] = $config;
			
			return $query->row;
		} else {
			return false;
		}
	}
	
	public function getRestaurantAddresses($restaurant_id){
		$address_data = array();
		
		$sql  = "SELECT ra.*, c.name AS country, ct.name AS city, d.name AS district FROM " . DB_PREFIX . "restaurant_address ra";
		$sql .= " LEFT JOIN " . DB_PREFIX . "country c ON (ra.country_id = c.country_id)";
		$sql .= " LEFT JOIN " . DB_PREFIX . "city ct ON (ra.city_id = ct.city_id)";
		$sql .= " LEFT JOIN " . DB_PREFIX . "district d ON (ra.district_id = d.district_id)";
		$sql .= " WHERE restaurant_id = '" . (int)$restaurant_id . "'";
		
		$query = $this->db->query($sql);

		foreach ($query->rows as $result) {
			$address_data[] = array(
				'address_id'             => $result['address_id'],
				'country_id'      => $result['country_id'],
				'city_id'     => $result['city_id'],
				'district_id' => $result['district_id'],
				'address' => $result['address'],
				'country' => $result['country'],
				'city' => $result['city'],
				'district' => $result['district']
			);
		}
		
		return $address_data;
	}
	
	public function getRestaurants($data = array()) {
		$cache = md5(http_build_query($data));
		$key = 'restaurant.' . $cache . (int)$this->city->getCityId();

		if(isset($data['filter_district_id']) && !empty($data['filter_district_id'])) {
			$key .= (int)$data['filter_district_id'];
		}

		$restaurant_data = $this->cache->get($key);
		
		if (!$restaurant_data) {
			$sql = "SELECT * FROM (";
			$sql .= "SELECT DISTINCT r.*, rd.name, rd.description, (SELECT AVG(rating) AS total FROM " . DB_PREFIX . "restaurant_review r1 WHERE r1.restaurant_id = r.restaurant_id AND r1.status = '1' GROUP BY r1.restaurant_id) AS rating ";
			
			if (sizeof($data['filter_option_value_ids']) > 0) {
				$sql .= " ,(SELECT COUNT(*) FROM " . DB_PREFIX . "restaurant_option ro WHERE r.restaurant_id = ro.restaurant_id";
				
				$sql .= " AND (";
				$i = 0;
				foreach ($data['filter_option_value_ids'] as $filter_option_value_id) {
					if ($i++ > 0){
						$sql .= " OR ";
					}
					$sql .= "ro.option_value_id = '" . (int)$filter_option_value_id . "'";
				}
				$sql .= ")";
				$sql .= ") AS total_option";
			}
			
			$sql .= " FROM " . DB_PREFIX . "restaurant r";
			$sql .= " LEFT JOIN " . DB_PREFIX . "restaurant_description rd ON (r.restaurant_id = rd.restaurant_id)"; 
			
			if((int)$this->city->getCityId() > 0 
				|| (isset($data['filter_district_id']) && !empty($data['filter_district_id']))) {
				$sql .= " LEFT JOIN " . DB_PREFIX . "restaurant_address ra ON (r.restaurant_id = ra.restaurant_id)";
			}
			
			if (!empty($data['filter_tag'])) {
				$sql .= " LEFT JOIN " . DB_PREFIX . "restaurant_tag rt ON (r.restaurant_id = rt.restaurant_id)";
			}
					
			$sql .= " WHERE rd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND r.status = '1'";
						
			// Seach for specific city
			if((int)$this->city->getCityId() > 0) {
				$sql .= " AND ra.city_id = '" . (int)$this->city->getCityId() . "'";
			}
		
			// Search for specific district
			if(isset($data['filter_district_id']) && !empty($data['filter_district_id'])) {
				$sql .= " AND ra.district_id = '" . (int)$data['filter_district_id'] . "'";
			}
			
			if (!empty($data['filter_name'])) {
				$sql .= " AND (";
											
				if (!empty($data['filter_name'])) {
					$implode = array();
					
					$words = explode(' ', $data['filter_name']);
					
					foreach ($words as $word) {
						$implode[] = "LCASE(rd.name) LIKE '%" . $this->db->escape(utf8_strtolower($word)) . "%'";
					}
					
					if ($implode) {
						$sql .= " " . implode(" OR ", $implode) . "";
					}
				}
				
				if (!empty($data['filter_name']) && !empty($data['filter_tag'])) {
					$sql .= " OR ";
				}
				
				if (!empty($data['filter_tag'])) {
					$implode = array();
					
					$words = explode(' ', $data['filter_tag']);
					
					foreach ($words as $word) {
						$implode[] = "LCASE(rt.tag) LIKE '%" . $this->db->escape(utf8_strtolower($data['filter_tag'])) . "%' AND rt.language_id = '" . (int)$this->config->get('config_language_id') . "'";
					}
					
					if ($implode) {
						$sql .= " " . implode(" OR ", $implode) . "";
					}
				}
			
				$sql .= ")";
			}
			$sql .= ") sub_table WHERE 1=1";
			
			if (sizeof($data['filter_option_value_ids']) > 0) {
				$sql .= " AND total_option = " . (int)sizeof($data['filter_option_value_ids']);
			}		
				
			$sort_data = array(
				'rd.name',
				'rating',
				'rating',
				'r.date_added'
			);	
			
			if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
				if ($data['sort'] == 'r.name' || $data['sort'] == 'r.model') {
					$sql .= " ORDER BY LCASE(" . $data['sort'] . ")";
				} else {
					$sql .= " ORDER BY " . $data['sort'];
				}
			} else {
				$sql .= " ORDER BY rating";	
			}
			
			if (isset($data['order']) && ($data['order'] == 'DESC')) {
				$sql .= " DESC";
			} else {
				$sql .= " ASC";
			}
		
			if (isset($data['start']) || isset($data['limit'])) {
				if ($data['start'] < 0) {
					$data['start'] = 0;
				}				
	
				if ($data['limit'] < 1) {
					$data['limit'] = 20;
				}	
			
				$sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
			}
			
			$restaurant_data = array();

			$query = $this->db->query($sql);
			
			foreach ($query->rows as $result) {
				$restaurant_data[] = $this->getRestaurant($result['restaurant_id']);
			}
			
			$this->cache->set($key, $restaurant_data);
		}
		
		return $restaurant_data;
	}
	
	public function getLatestRestaurants($limit) {
		$key = 'restaurant.latest.' . (int)$this->config->get('config_language_id') . '.' . (int)$this->config->get('config_language_id') . '.' . (int)$this->city->getCityId() . '.' . (int)$limit; 
		
		$restaurant_data = $this->cache->get($key);

		if (!$restaurant_data) {
			$sql = "SELECT *, " .
			   	   "	(SELECT AVG(rating) AS total FROM " . DB_PREFIX . "restaurant_review r1 WHERE r1.restaurant_id = r.restaurant_id AND r1.status = '1' GROUP BY r1.restaurant_id) AS rating," .
			       " 	(SELECT COUNT(*) AS total FROM " . DB_PREFIX . "restaurant_review r2 WHERE r2.restaurant_id = r.restaurant_id AND r2.status = '1' GROUP BY r2.restaurant_id) AS reviews" .
			   	   " FROM " . DB_PREFIX . "restaurant r LEFT JOIN " . DB_PREFIX . "restaurant_description rd ON r.restaurant_id = rd.restaurant_id";
			
			if((int)$this->city->getCityId() > 0) {
				$sql .= " LEFT JOIN " . DB_PREFIX . "restaurant_address ra ON (r.restaurant_id = ra.restaurant_id)";
			}
			
			$sql .= " WHERE r.status = '1' AND rd.language_id = '" . (int)$this->config->get('config_language_id') . "'";
			
			// Search for specific city
			if((int)$this->city->getCityId() > 0) {
				$sql .= " AND ra.city_id = '" . (int)$this->city->getCityId() . "'";
			}
			
			$sql .= " ORDER BY r.date_added DESC LIMIT " . (int)$limit;
			
			$query = $this->db->query($sql);
		 	
			foreach ($query->rows as $result) {
				$result['rating'] = (int)$result['rating'];
				
				// Settings
				$setting_query = $this->db->query("SELECT * FROM " . DB_PREFIX . "setting WHERE restaurant_id = '" . (int)$result['restaurant_id'] . "'");
			
				// Config
				$config = new Config();
				foreach ($setting_query->rows as $setting) {
					if (!$setting['serialized']) {
						$config->set($setting['key'], $setting['value']);
					} else {
						$config->set($setting['key'], unserialize($setting['value']));
					}
				}
				
				$result['config'] = $config;
				
				$restaurant_data[$result['restaurant_id']] = $result;
			}
			
			$this->cache->set($key, $restaurant_data);
		}
		
		return $restaurant_data;
	}
	
	public function getPopularRestaurants($limit) {
		$restaurant_data = array();
		
		$query = $this->db->query("SELECT r.restaurant_id FROM " . DB_PREFIX . "restaurant r LEFT JOIN " . DB_PREFIX . "restaurant_to_restaurant p2s ON (r.restaurant_id = p2s.restaurant_id) WHERE r.status = '1' AND r.date_available <= NOW() AND p2s.restaurant_id = '" . (int)$this->config->get('config_restaurant_id') . "' ORDER BY r.viewed, r.date_added DESC LIMIT " . (int)$limit);
		
		foreach ($query->rows as $result) { 		
			$restaurant_data[$result['restaurant_id']] = $this->getRestaurant($result['restaurant_id']);
		}
					 	 		
		return $restaurant_data;
	}

	public function getBestSellerRestaurants($limit) {
		$restaurant_data = $this->cache->get('restaurant.bestseller.' . (int)$this->config->get('config_language_id') . '.' . (int)$this->config->get('config_restaurant_id') . '.' . (int)$limit);

		if (!$restaurant_data) { 
			$restaurant_data = array();
			
			$query = $this->db->query("SELECT op.restaurant_id, COUNT(*) AS total FROM " . DB_PREFIX . "order_restaurant op LEFT JOIN `" . DB_PREFIX . "order` o ON (op.order_id = o.order_id) LEFT JOIN `" . DB_PREFIX . "restaurant` r ON (op.restaurant_id = r.restaurant_id) LEFT JOIN " . DB_PREFIX . "restaurant_to_restaurant p2s ON (r.restaurant_id = p2s.restaurant_id) WHERE o.order_status_id > '0' AND r.status = '1' AND r.date_available <= NOW() AND p2s.restaurant_id = '" . (int)$this->config->get('config_restaurant_id') . "' GROUP BY op.restaurant_id ORDER BY total DESC LIMIT " . (int)$limit);
			
			foreach ($query->rows as $result) { 		
				$restaurant_data[$result['restaurant_id']] = $this->getRestaurant($result['restaurant_id']);
			}
			
			$this->cache->set('restaurant.bestseller.' . (int)$this->config->get('config_language_id') . '.' . (int)$this->config->get('config_restaurant_id') . '.' . (int)$limit, $restaurant_data);
		}
		
		return $restaurant_data;
	}
	
	public function getRestaurantOptions($restaurant_id) {
		$restaurant_option_data = array();
		
		$this->load->model('restaurant/option');
		
		$data = array(
			'group' => 'restaurant',
			'restaurant_id' => $restaurant_id
		);
		
		$restaurant_options = $this->model_restaurant_option->getOptions($data);
		
		foreach ($restaurant_options as $restaurant_option) {
			if ($restaurant_option['type'] == 'select' || $restaurant_option['type'] == 'radio' || $restaurant_option['type'] == 'checkbox' || $restaurant_option['type'] == 'image') {
				$restaurant_option_value_data = array();
				
				$data = array(
					'option_id' => $restaurant_option['option_id'],
					'restaurant_id' => $restaurant_id
				);
				
				$otion_values = $this->model_restaurant_option->getOptionValues($data);
			
				foreach ($otion_values as $restaurant_option_value) {
					$restaurant_option_value_data[] = array(
						'option_id' 			  => $restaurant_option['option_id'],
						'option_value_id'         => $restaurant_option_value['option_value_id'],
						'name'                    => $restaurant_option_value['name'],
						'image'                   => $restaurant_option_value['image'],
						'total_restaurant'        => $restaurant_option_value['total_restaurant']
					);
				}
				
				$restaurant_option_data[$restaurant_option['option_id']] = array(
					'option_id'         => $restaurant_option['option_id'],
					'category'          => $restaurant_option['category'],
					'name'              => $restaurant_option['name'],
					'image'             => $restaurant_option['image'],
					'type'              => $restaurant_option['type'],
					'option_value'      => $restaurant_option_value_data
				);
			} else {
				$restaurant_option_data[] = array(
					'option_id'         => $restaurant_option['option_id'],
					'category'          => $restaurant_option['category'],
					'name'              => $restaurant_option['name'],
					'image'             => $restaurant_option['image'],
					'type'              => $restaurant_option['type'],
					'option_value'      => $restaurant_option['option_value']
				);				
			}
      	}
		
		return $restaurant_option_data;
	}
	
	public function getRestaurantDiscounts($restaurant_id) {
		if ($this->customer->isLogged()) {
			$customer_group_id = $this->customer->getCustomerGroupId();
		} else {
			$customer_group_id = $this->config->get('config_customer_group_id');
		}	
		
		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "restaurant_discount WHERE restaurant_id = '" . (int)$restaurant_id . "' AND customer_group_id = '" . (int)$customer_group_id . "' AND quantity > 1 AND ((date_start = '0000-00-00' OR date_start < NOW()) AND (date_end = '0000-00-00' OR date_end > NOW())) ORDER BY quantity ASC, priority ASC, price ASC");

		return $query->rows;		
	}
		
	public function getRestaurantImages($restaurant_id) {
		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "restaurant_image WHERE restaurant_id = '" . (int)$restaurant_id . "' ORDER BY sort_order ASC");

		return $query->rows;
	}
	
	public function getRestaurantRelated($restaurant_id) {
		$restaurant_data = array();

		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "restaurant_related pr LEFT JOIN " . DB_PREFIX . "restaurant r ON (pr.related_id = r.restaurant_id) LEFT JOIN " . DB_PREFIX . "restaurant_to_restaurant p2s ON (r.restaurant_id = p2s.restaurant_id) WHERE pr.restaurant_id = '" . (int)$restaurant_id . "' AND r.status = '1' AND r.date_available <= NOW() AND p2s.restaurant_id = '" . (int)$this->config->get('config_restaurant_id') . "'");
		
		foreach ($query->rows as $result) { 
			$restaurant_data[$result['related_id']] = $this->getRestaurant($result['related_id']);
		}
		
		return $restaurant_data;
	}
		
	public function getRestaurantTags($restaurant_id) {
		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "restaurant_tag WHERE restaurant_id = '" . (int)$restaurant_id . "' AND language_id = '" . (int)$this->config->get('config_language_id') . "'");

		return $query->rows;
	}
		
	public function getTotalRestaurants($data = array()) {
		$sql = "SELECT COUNT(DISTINCT restaurant_id) AS total FROM (";
		$sql .= "SELECT r.restaurant_id ";

		if (sizeof($data['filter_option_value_ids']) > 0) {
			$sql .= " ,(SELECT COUNT(*) FROM " . DB_PREFIX . "restaurant_option ro WHERE r.restaurant_id = ro.restaurant_id";
			
			$sql .= " AND (";
			$i = 0;
			foreach ($data['filter_option_value_ids'] as $filter_option_value_id) {
				if ($i++ > 0){
					$sql .= " OR ";
				}
				$sql .= "ro.option_value_id = '" . (int)$filter_option_value_id . "'";
			}
			$sql .= ")";
			$sql .= ") AS total_option";
		}
		
		$sql .= " FROM " . DB_PREFIX . "restaurant r LEFT JOIN " . DB_PREFIX . "restaurant_description rd ON (r.restaurant_id = rd.restaurant_id)";
	
		if((int)$this->city->getCityId() > 0 
			|| (isset($data['filter_district_id']) && !empty($data['filter_district_id']))) {
			$sql .= " LEFT JOIN " . DB_PREFIX . "restaurant_address ra ON (r.restaurant_id = ra.restaurant_id)";
		}
			
		if (!empty($data['filter_tag'])) {
			$sql .= " LEFT JOIN " . DB_PREFIX . "restaurant_tag rt ON (r.restaurant_id = rt.restaurant_id)";			
		}
		
		$sql .= " WHERE rd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND r.status = '1'";
		
		// Search for specific city
		if((int)$this->city->getCityId() > 0) {
			$sql .= " AND ra.city_id = '" . (int)$this->city->getCityId() . "'";
		}
		
		// Search for specific district
		if(isset($data['filter_district_id']) && !empty($data['filter_district_id'])) {
			$sql .= " AND ra.district_id = '" . (int)$data['filter_district_id'] . "'";
		}
			
		if (!empty($data['filter_name']) || !empty($data['filter_tag'])) {
			$sql .= " AND (";
								
			if (!empty($data['filter_name'])) {
				$implode = array();
				
				$words = explode(' ', $data['filter_name']);
				
				foreach ($words as $word) {
					if (!empty($data['filter_description'])) {
						$implode[] = "LCASE(rd.name) LIKE '%" . $this->db->escape(utf8_strtolower($word)) . "%' OR LCASE(rd.description) LIKE '%" . $this->db->escape(utf8_strtolower($word)) . "%'";
					} else {
						$implode[] = "LCASE(rd.name) LIKE '%" . $this->db->escape(utf8_strtolower($word)) . "%'";
					}				
				}
				
				if ($implode) {
					$sql .= " " . implode(" OR ", $implode) . "";
				}
			}
			
			if (!empty($data['filter_name']) && !empty($data['filter_tag'])) {
				$sql .= " OR ";
			}
			
			if (!empty($data['filter_tag'])) {
				$implode = array();
				
				$words = explode(' ', $data['filter_tag']);
				
				foreach ($words as $word) {
					$implode[] = "LCASE(rt.tag) LIKE '%" . $this->db->escape(utf8_strtolower($data['filter_tag'])) . "%' AND rt.language_id = '" . (int)$this->config->get('config_language_id') . "'";
				}
				
				if ($implode) {
					$sql .= " " . implode(" OR ", $implode) . "";
				}
			}
		
			$sql .= ")";
		}
		$sql .= ") sub_table WHERE 1=1";
			
		if (sizeof($data['filter_option_value_ids']) > 0) {
			$sql .= " AND total_option = " . (int)sizeof($data['filter_option_value_ids']);
		}
		
		$query = $this->db->query($sql);
		
		return $query->row['total'];
	}	
}
?>